package com.lzd.main.dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import cn.hutool.json.JSONArray;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class AntiFakeDao {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	public void save(long id, String saltKey, long time) throws SQLException {
		DataSource ds = jdbcTemplate.getDataSource();
		Connection conn = null;
		PreparedStatement st = null;
		try{
			conn = ds.getConnection();

			st = conn.prepareStatement("INSERT INTO prods_code_t (code_id,prod_id,anti_code,create_time,bind_time) VALUES (?,?,?,?,?)");
			st.setLong(1, id);
			st.setLong(2, id);
			st.setString(3, saltKey);
			st.setDate(4, new Date(time));
			st.setTimestamp(5, new Timestamp(time));
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally{
			if(st!=null){
				try{
					//关闭负责执行SQL命令的Statement对象
					st.close();
					st = null;
				}catch (Exception e) {
					e.printStackTrace();
				}
			}
			if(conn!=null) {
				try{
					conn.close();
				}catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	public AntiFake getById(long id) throws SQLException {

		AntiFake data = null;
		DataSource ds = jdbcTemplate.getDataSource();
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
        try{
        	conn = ds.getConnection();
        	
        	st = conn.prepareStatement("SELECT code_id,prod_id,create_time,anti_code,route FROM prods_code_t WHERE code_id=?");
			st.setLong(1, id);
			rs = st.executeQuery();
			if (rs.next()) {
				data = new AntiFake();
				data.setId(rs.getLong("code_id"));
				data.setCode(rs.getString("anti_code"));
				data.setDate(rs.getDate("create_time"));
				data.setRoute(rs.getString("route"));
			}

			st = conn.prepareStatement("SELECT point_lat,point_lng FROM prod_track_t WHERE code_id=? ORDER BY id DESC LIMIT 1");
			st.setLong(1, id);
			rs = st.executeQuery();
			if (rs.next()) {
				data.setPointLng(rs.getDouble("point_lng"));
				data.setPointLat(rs.getDouble("point_lat"));
			}
        } catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally{
            //释放资源
			if(rs!=null) {
        		try{
        			rs.close();
                }catch (Exception e) {
                    e.printStackTrace();
                }
        	}
            if(st!=null){
                try{
                    //关闭负责执行SQL命令的Statement对象
                    st.close();
                    st = null;
                }catch (Exception e) {
                    e.printStackTrace();
                }
            }
        	if(conn!=null) {
        		try{
        			conn.close();
        		}catch (Exception e) {
                    e.printStackTrace();
                }
        	}
        }
		return data;
	}

	public List<TrackModel> getList(long codeId, String address, double lng, double lat, JSONArray jsonObject) throws SQLException {

		List<TrackModel> list = new ArrayList<>();
		DataSource ds = jdbcTemplate.getDataSource();
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try{
			conn = ds.getConnection();

			st = conn.prepareStatement("UPDATE prods_code_t SET route=? WHERE code_id=?");
			st.setString(1, jsonObject.toString());
			st.setLong(2, codeId);
			st.executeUpdate();

			st = conn.prepareStatement("INSERT INTO prod_track_t (type,time,code_id,address,point_lat,point_lng) VALUES (?,?,?,?,?,?)");
			st.setInt(1, 1);
			st.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
			st.setLong(3, codeId);
			st.setString(4, address);
			st.setDouble(5, lng);
			st.setDouble(6, lat);
			st.executeUpdate();

			//System.out.println("codeid："+codeId);
			st = conn.prepareStatement("SELECT type,time,address,point_lat,point_lng FROM prod_track_t WHERE code_id=?");
			st.setLong(1, codeId);
			rs = st.executeQuery();
			while (rs.next()) {
				TrackModel data = new TrackModel();
				data.setType(rs.getInt("type"));
				data.setTime(rs.getTimestamp("time"));
				data.setAddress(rs.getString("address"));
				data.setPointLng(rs.getDouble("point_lng"));
				data.setPointLat(rs.getDouble("point_lat"));
				list.add(data);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally{
			//释放资源
			if(rs!=null) {
				try{
					rs.close();
				}catch (Exception e) {
					e.printStackTrace();
				}
			}
			if(st!=null){
				try{
					//关闭负责执行SQL命令的Statement对象
					st.close();
					st = null;
				}catch (Exception e) {
					e.printStackTrace();
				}
			}
			if(conn!=null) {
				try{
					conn.close();
				}catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
		return list;
	}

	public void addErrRecord(long codeId, String address,double lng,double lat) throws SQLException {

		List<TrackModel> list = new ArrayList<>();
		DataSource ds = jdbcTemplate.getDataSource();
		Connection conn = null;
		PreparedStatement st = null;
		try{
			conn = ds.getConnection();

			st = conn.prepareStatement("INSERT INTO prod_track_t (type,time,code_id,address,point_lat,point_lng) VALUES (?,?,?,?,?,?)");
			st.setInt(1, 1);
			st.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
			st.setLong(3, codeId);
			st.setString(4, address);
			st.setDouble(5, lng);
			st.setDouble(6, lat);
			st.executeUpdate();


		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally{

			if(st!=null){
				try{
					//关闭负责执行SQL命令的Statement对象
					st.close();
					st = null;
				}catch (Exception e) {
					e.printStackTrace();
				}
			}
			if(conn!=null) {
				try{
					conn.close();
				}catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}
}
